﻿Imports System.Data.SqlClient

Public Class FrmTimKiemBanDoDiaChinh
    Private Sub NapDMHienTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHienTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHienTrang.DisplayMember = "TenHienTrang"
        ComboBoxHienTrang.ValueMember = "MaHienTrang"
        ComboBoxHienTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewBanDoDiaChinh()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select " &
                                "   MasoBD " &
                                "  ,NgayNhap " &
                                "  ,TongToBanDo " &
                                "  ,TongThua " &
                                "  ,TongDienTich " &
                                "  ,NamThanhLap " &
                                "  ,DonViDo " &
                                "  ,DiaDanh " &
                                "  ,ToBanDoSo " &
                                "  ,TenTyLe " &
                                "  ,ht.TenHienTrang " &
                                "  ,tli.TenLoaiTaiLieu " &
                                "  ,ke.tenke " &
                                "  ,k.TenKho " &
                                " From  BanDoDiaChinh dc, DMTyLe tl, DMHienTrang ht, DMTaiLieu tli, DMKho k, DMKe ke, tenxa tx, tenhuyen th " &
                                " WHERE dc.tyleid = tl.matyle " &
                                " and dc.MaLoaiTaiLieuID = tli.maloaiTaiLieuID " &
                                " and dc.makhoid = k.makhoid " &
                                " and dc.maKeid = ke.makeid " &
                                " and MaHuyenId = '" & ComboBoxHuyen.SelectedValue.ToString & "' " &
                                " and dc.mahuyenid = th.maH " &
                                " and MaXaId = '" & ComboBoxXa.SelectedValue.ToString & "' " &
                                " and dc.maxaid = tx.maxa " &
                                " and dc.diadanh  like N'%" & TextBoxDiaDanh.Text & "%'" &
                                " and dc.mahientrangid = '" & ComboBoxHienTrang.SelectedValue.ToString & "'" &
                                " and dc.maHienTrangID = ht.mahienTrang ", ob_cnn)

        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBanDoDiaChinh.DataSource = tbl
        'If (tbl.Rows.Count = 0) Then
        '    MessageBox.Show("Không tìm thấy thông tin !")
        'Else
        'End If
        ob_cnn.Close()
    End Sub
    Private Sub XuatExcel()
        ' xuat truc tiep ra excel
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = "Ban do"
                Dim i As Integer = 1
                For col = 0 To DataGridViewBanDoDiaChinh.Columns.Count - 1
                    .cells(1, i).value = DataGridViewBanDoDiaChinh.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To DataGridViewBanDoDiaChinh.Columns.Count - 1
                    i = 2
                    For row = 0 To DataGridViewBanDoDiaChinh.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = DataGridViewBanDoDiaChinh.Rows(row).Cells(col).Value
                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu xuất ra Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub

    Private Sub FrmTimKiemBanDoDiaChinh_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapDMHienTrang()
        NapDonViHuyen()
    End Sub

    Private Sub ComboBoxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        ' load ten huyen
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        NapDataGridViewBanDoDiaChinh()

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Me.Close()
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        XuatExcel()
    End Sub

    Private Sub TextBoxDiaDanh_TextChanged(sender As Object, e As EventArgs) Handles TextBoxDiaDanh.TextChanged
        NapDataGridViewBanDoDiaChinh()
    End Sub
End Class